from flask import Flask, request, jsonify, render_template, session, redirect, url_for
from flask_cors import CORS
import sqlite3
import json
import os
from datetime import datetime
import threading
import time

# 兼容导入数据库驱动
try:
    import pyodbc
except Exception:
    pyodbc = None

try:
    import pymysql
except Exception:
    pymysql = None

# 兼容导入pymssql（无需ODBC驱动）
try:
    import pymssql
except Exception:
    pymssql = None

app = Flask(__name__)
app.secret_key = 'mssql2mysql_secret_key_2024'
CORS(app)

# 迁移进度信息（内存）
progress_info = {}

# 初始化SQLite数据库
def init_db():
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    
    # 创建配置表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS configurations (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            mssql_host TEXT,
            mssql_database TEXT,
            mssql_port INTEGER,
            mssql_username TEXT,
            mssql_password TEXT,
            mysql_host TEXT,
            mysql_database TEXT,
            mysql_port INTEGER,
            mysql_username TEXT,
            mysql_password TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            final_status TEXT DEFAULT 'pending',
            final_message TEXT,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 创建迁移记录表
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS migration_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            config_id INTEGER,
            status TEXT,
            message TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (config_id) REFERENCES configurations (id)
        )
    ''')
    
    conn.commit()
    conn.close()

# 主页路由
@app.route('/')
def index():
    return render_template('index.html')

# 管理后台路由
@app.route('/admin')
def admin_login():
    if 'admin_logged_in' in session:
        return render_template('admin.html')
    return render_template('admin_login.html')

@app.route('/admin/login', methods=['POST'])
def admin_login_post():
    data = request.get_json()
    username = data.get('username')
    password = data.get('password')
    
    if username == 'admin' and password == 'admin123':
        session['admin_logged_in'] = True
        return jsonify({'success': True})
    else:
        return jsonify({'success': False, 'message': '用户名或密码错误'})

@app.route('/admin/logout')
def admin_logout():
    session.pop('admin_logged_in', None)
    return redirect(url_for('admin_login'))

# API路由
@app.route('/api/migrate', methods=['POST'])
def migrate_database():
    data = request.get_json()
    
    # 保存配置到数据库
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT INTO configurations 
        (mssql_host, mssql_database, mssql_port, mssql_username, mssql_password,
         mysql_host, mysql_database, mysql_port, mysql_username, mysql_password)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    ''', (
        data['mssql']['host'], data['mssql']['database'], data['mssql']['port'],
        data['mssql']['username'], data['mssql']['password'],
        data['mysql']['host'], data['mysql']['database'], data['mysql']['port'],
        data['mysql']['username'], data['mysql']['password']
    ))
    
    config_id = cursor.lastrowid
    conn.commit()
    conn.close()

    # 初始化进度
    progress_info[config_id] = {
        'status': 'pending',
        'total': 0,
        'done': 0,
        'start_time': datetime.now().isoformat(),
        'last_update': datetime.now().isoformat(),
        'eta_seconds': None,
        'message': ''
    }

    # 启动迁移任务
    thread = threading.Thread(target=perform_migration, args=(config_id, data))
    thread.start()
    
    return jsonify({'success': True, 'message': '迁移任务已启动', 'config_id': config_id})

def perform_migration(config_id, config_data):
    """执行数据库迁移"""
    mssql_conn = None
    mysql_conn = None

    try:
        log_migration(config_id, 'running', '开始连接MSSQL数据库...')

        # 检查驱动可用性
        if pymysql is None:
            msg = '迁移失败: PyMySQL 未安装或不可用'
            progress_info.get(config_id, {}).update({'status': 'failed', 'message': msg, 'last_update': datetime.now().isoformat()})
            log_migration(config_id, 'failed', msg)
            return

        # 选择可用ODBC驱动（优先18，其次17，其次FreeTDS）
        use_pyodbc = False
        chosen = None
        available = []

        if pyodbc is not None:
            try:
                available = pyodbc.drivers()
            except Exception:
                available = []
            preferred = ['ODBC Driver 18 for SQL Server', 'ODBC Driver 17 for SQL Server', 'FreeTDS']
            for name in preferred:
                if name in available:
                    chosen = name
                    use_pyodbc = True
                    break

        if use_pyodbc:
            log_migration(config_id, 'running', f'使用ODBC驱动: {chosen}')
            if chosen == 'FreeTDS':
                connection_string = (
                    f"DRIVER={{{chosen}}};SERVER={config_data['mssql']['host']};"
                    f"PORT={config_data['mssql']['port']};DATABASE={config_data['mssql']['database']};"
                    f"UID={config_data['mssql']['username']};PWD={config_data['mssql']['password']};"
                    f"TDS_Version=7.4"
                )
            else:
                connection_string = (
                    f"DRIVER={{{chosen}}};SERVER={config_data['mssql']['host']},{config_data['mssql']['port']};"
                    f"DATABASE={config_data['mssql']['database']};UID={config_data['mssql']['username']};"
                    f"PWD={config_data['mssql']['password']};TrustServerCertificate=yes;Encrypt=no"
                )
            mssql_conn = pyodbc.connect(connection_string, timeout=30)
        else:
            if pymssql is None:
                msg = '迁移失败: 未找到可用ODBC驱动，且pymssql不可用。请安装msodbcsql18或tdsodbc，或安装pymssql。'
                progress_info.get(config_id, {}).update({'status': 'failed', 'message': msg, 'last_update': datetime.now().isoformat()})
                log_migration(config_id, 'failed', msg)
                return
            log_migration(config_id, 'running', 'ODBC驱动不可用，改用pymssql连接MSSQL')
            mssql_conn = pymssql.connect(
                server=config_data['mssql']['host'],
                user=config_data['mssql']['username'],
                password=config_data['mssql']['password'],
                database=config_data['mssql']['database'],
                port=config_data['mssql']['port'],
                timeout=30
            )

        log_migration(config_id, 'running', 'MSSQL连接成功，开始连接MySQL数据库...')

        # 连接MySQL
        mysql_conn = pymysql.connect(
            host=config_data['mysql']['host'],
            user=config_data['mysql']['username'],
            password=config_data['mysql']['password'],
            database=config_data['mysql']['database'],
            port=config_data['mysql']['port'],
            charset='utf8mb4',
            connect_timeout=30
        )

        log_migration(config_id, 'running', 'MySQL连接成功，开始获取表结构...')

        # 获取MSSQL表列表
        mssql_cursor = mssql_conn.cursor()
        mssql_cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'")
        tables = [row[0] for row in mssql_cursor.fetchall()]

        # 更新总表数量
        progress = progress_info.get(config_id)
        if progress is not None:
            progress['status'] = 'running'
            progress['total'] = len(tables)
            progress['done'] = 0
            progress['last_update'] = datetime.now().isoformat()

        log_migration(config_id, 'running', f'发现 {len(tables)} 个表，开始迁移...')

        mysql_cursor = mysql_conn.cursor()

        start_time = time.time()
        for idx, table in enumerate(tables, start=1):
            try:
                log_migration(config_id, 'running', f'正在迁移表: {table}')

                # 获取表结构（包含精度和标度信息）
                mssql_cursor.execute("""
                    SELECT
                        COLUMN_NAME,
                        DATA_TYPE,
                        IS_NULLABLE,
                        CHARACTER_MAXIMUM_LENGTH,
                        NUMERIC_PRECISION,
                        NUMERIC_SCALE
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = ? AND TABLE_SCHEMA = 'dbo'
                    ORDER BY ORDINAL_POSITION
                """, (table,))
                columns = mssql_cursor.fetchall()

                if not columns:
                    log_migration(config_id, 'warning', f'表 {table} 没有找到列信息，跳过')
                    continue

                # 创建MySQL表（先删除已存在的表以确保覆盖）
                try:
                    mysql_cursor.execute(f"DROP TABLE IF EXISTS `{table}`")
                    mysql_conn.commit()
                    log_migration(config_id, 'running', f'已删除表 {table}（如果存在）')
                except Exception as drop_error:
                    log_migration(config_id, 'warning', f'删除表 {table} 时出错: {str(drop_error)}')

                # 构建列定义和列名映射
                create_sql = f"CREATE TABLE `{table}` ("
                column_defs = []
                column_name_map = {}  # 原始列名 -> MySQL列名的映射
                seen_columns = set()  # 用于检测重复列名

                for col in columns:
                    col_name = col[0]
                    data_type = col[1]
                    is_nullable = col[2]
                    max_length = col[3] if len(col) > 3 else None
                    numeric_precision = col[4] if len(col) > 4 else None
                    numeric_scale = col[5] if len(col) > 5 else None

                    # 处理重复列名
                    original_col_name = col_name
                    counter = 1
                    while col_name.lower() in seen_columns:
                        col_name = f"{original_col_name}_{counter}"
                        counter += 1
                        if counter > 10:  # 防止无限循环
                            log_migration(config_id, 'warning', f'表 {table} 列名 {original_col_name} 重复次数过多，跳过')
                            break

                    if counter <= 10:  # 只有在找到合适列名时才添加
                        seen_columns.add(col_name.lower())
                        column_name_map[original_col_name] = col_name

                        # 转换数据类型
                        mysql_type = convert_data_type(data_type, max_length, numeric_precision, numeric_scale)
                        nullable = "NULL" if is_nullable == "YES" else "NOT NULL"
                        column_defs.append(f"`{col_name}` {mysql_type} {nullable}")

                        if col_name != original_col_name:
                            log_migration(config_id, 'warning', f'表 {table} 列名 {original_col_name} 重复，重命名为 {col_name}')

                if not column_defs:
                    log_migration(config_id, 'error', f'表 {table} 没有有效列定义，跳过')
                    continue

                # 获取主键信息
                try:
                    mssql_cursor.execute("""
                        SELECT COLUMN_NAME
                        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                        WHERE TABLE_NAME = ?
                        AND TABLE_SCHEMA = 'dbo'
                        AND CONSTRAINT_NAME LIKE 'PK_%'
                        ORDER BY ORDINAL_POSITION
                    """, (table,))
                    pk_columns = [column_name_map.get(row[0], row[0]) for row in mssql_cursor.fetchall() if row[0] in column_name_map]

                    if pk_columns:
                        pk_def = ", ".join([f"`{col}`" for col in pk_columns])
                        column_defs.append(f"PRIMARY KEY ({pk_def})")
                        log_migration(config_id, 'running', f'表 {table} 添加主键: {", ".join(pk_columns)}')
                except Exception as pk_error:
                    log_migration(config_id, 'warning', f'获取表 {table} 主键信息失败: {str(pk_error)}')

                create_sql += ", ".join(column_defs) + ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"

                try:
                    mysql_cursor.execute(create_sql)
                    mysql_conn.commit()
                    log_migration(config_id, 'running', f'表 {table} 结构创建成功 ({len([d for d in column_defs if not d.startswith("PRIMARY KEY")])} 列)')
                except Exception as table_error:
                    error_msg = str(table_error)
                    log_migration(config_id, 'error', f'创建表 {table} 失败: {error_msg}')
                    log_migration(config_id, 'error', f'失败的SQL: {create_sql[:500]}...')
                    # 继续处理下一个表，不中断整个迁移过程
                    continue

                # 迁移数据
                try:
                    # 获取数据行数
                    mssql_cursor.execute(f"SELECT COUNT(*) FROM [{table}]")
                    row_count = mssql_cursor.fetchone()[0]

                    if row_count > 0:
                        # 分批迁移数据，避免内存溢出
                        batch_size = 1000
                        offset = 0
                        total_inserted = 0

                        # 获取原始列名（从MSSQL）
                        original_columns = [col[0] for col in columns if col[0] in column_name_map]

                        # 获取MySQL列名（映射后的）
                        mysql_columns = [column_name_map[col] for col in original_columns]

                        if not original_columns:
                            log_migration(config_id, 'error', f'表 {table} 没有有效的列名，跳过数据迁移')
                        else:
                            while offset < row_count:
                                # 使用原始列名从MSSQL查询
                                mssql_column_list = ", ".join([f"[{col}]" for col in original_columns])

                                try:
                                    # 使用参数化查询构建SQL
                                    query_sql = f"SELECT {mssql_column_list} FROM [{table}] ORDER BY (SELECT NULL) OFFSET ? ROWS FETCH NEXT ? ROWS ONLY"
                                    mssql_cursor.execute(query_sql, (offset, batch_size))
                                    rows = mssql_cursor.fetchall()
                                except Exception as query_error:
                                    log_migration(config_id, 'error', f'表 {table} 数据查询失败 (offset={offset}): {str(query_error)}')
                                    break

                                if not rows:
                                    break

                                # 数据清洗和转换
                                cleaned_rows = []
                                for row in rows:
                                    cleaned_row = []
                                    for i, value in enumerate(row):
                                        # 处理特殊值
                                        if value is None:
                                            cleaned_row.append(None)
                                        elif isinstance(value, bytes):
                                            # 二进制数据保持不变
                                            cleaned_row.append(value)
                                        elif isinstance(value, str):
                                            # 字符串数据，处理特殊字符
                                            cleaned_row.append(value)
                                        elif isinstance(value, (int, float)):
                                            cleaned_row.append(value)
                                        elif hasattr(value, 'isoformat'):
                                            # 日期时间类型
                                            cleaned_row.append(value)
                                        else:
                                            # 其他类型转换为字符串
                                            cleaned_row.append(str(value))
                                    cleaned_rows.append(tuple(cleaned_row))

                                # 使用MySQL列名插入
                                mysql_column_list = ", ".join([f"`{col}`" for col in mysql_columns])
                                placeholders = ", ".join(["%s"] * len(mysql_columns))
                                insert_sql = f"INSERT INTO `{table}` ({mysql_column_list}) VALUES ({placeholders})"

                                try:
                                    mysql_cursor.executemany(insert_sql, cleaned_rows)
                                    mysql_conn.commit()
                                    total_inserted += len(cleaned_rows)
                                except Exception as data_error:
                                    log_migration(config_id, 'warning', f'表 {table} 批量插入失败 (offset={offset}): {str(data_error)}')
                                    # 尝试逐行插入
                                    success_count = 0
                                    for row_idx, row in enumerate(cleaned_rows):
                                        try:
                                            mysql_cursor.execute(insert_sql, row)
                                            mysql_conn.commit()
                                            success_count += 1
                                        except Exception as row_error:
                                            log_migration(config_id, 'warning', f'表 {table} 第 {offset + row_idx + 1} 行插入失败: {str(row_error)[:100]}')
                                    total_inserted += success_count
                                    if success_count > 0:
                                        log_migration(config_id, 'running', f'表 {table} 逐行插入成功 {success_count}/{len(cleaned_rows)} 行')

                                offset += batch_size

                                # 定期报告进度
                                if offset % 5000 == 0:
                                    log_migration(config_id, 'running', f'表 {table} 已迁移 {total_inserted}/{row_count} 行')

                            log_migration(config_id, 'running', f'表 {table} 数据迁移完成 ({total_inserted}/{row_count} 行)')
                    else:
                        log_migration(config_id, 'running', f'表 {table} 无数据，跳过数据迁移')

                except Exception as data_error:
                    log_migration(config_id, 'warning', f'表 {table} 数据迁移失败: {str(data_error)}')
                    import traceback
                    log_migration(config_id, 'error', f'详细错误: {traceback.format_exc()[:500]}')
                    # 继续处理下一个表
                
            except Exception as table_error:
                log_migration(config_id, 'error', f'处理表 {table} 时发生错误: {str(table_error)}')
                # 继续处理下一个表，不中断整个迁移过程
                continue

            # 更新进度
            progress = progress_info.get(config_id)
            if progress is not None:
                progress['done'] = idx
                elapsed = time.time() - start_time
                avg_per_table = elapsed / idx if idx > 0 else 0
                remaining = (progress['total'] - progress['done'])
                progress['eta_seconds'] = max(0, int(avg_per_table * remaining))
                progress['last_update'] = datetime.now().isoformat()

        log_migration(config_id, 'completed', f'数据库迁移完成！共迁移 {len(tables)} 个表')

        # 更新配置表中的最终状态
        update_config_status(config_id, 'completed', '迁移完成')

        progress = progress_info.get(config_id)
        if progress is not None:
            progress['status'] = 'completed'
            progress['message'] = '迁移完成'
            progress['eta_seconds'] = 0
            progress['last_update'] = datetime.now().isoformat()

    except Exception as e:
        import traceback
        error_detail = traceback.format_exc()
        log_migration(config_id, 'failed', f'迁移失败: {str(e)}')
        log_migration(config_id, 'error', f'错误详情: {error_detail[:1000]}')

        # 更新配置表中的最终状态
        update_config_status(config_id, 'failed', str(e))

        progress = progress_info.get(config_id)
        if progress is not None:
            progress['status'] = 'failed'
            progress['message'] = f'{str(e)}'
            progress['last_update'] = datetime.now().isoformat()

    finally:
        # 确保连接被正确关闭
        try:
            if mssql_conn:
                mssql_conn.close()
                log_migration(config_id, 'running', 'MSSQL连接已关闭')
        except Exception as close_error:
            log_migration(config_id, 'warning', f'关闭MSSQL连接时出错: {str(close_error)}')

        try:
            if mysql_conn:
                mysql_conn.close()
                log_migration(config_id, 'running', 'MySQL连接已关闭')
        except Exception as close_error:
            log_migration(config_id, 'warning', f'关闭MySQL连接时出错: {str(close_error)}')

def convert_data_type(mssql_type, max_length, numeric_precision=None, numeric_scale=None):
    """转换MSSQL数据类型到MySQL

    Args:
        mssql_type: MSSQL数据类型
        max_length: 字符/二进制类型的最大长度
        numeric_precision: 数值类型的精度
        numeric_scale: 数值类型的标度

    Returns:
        MySQL数据类型字符串
    """
    if not mssql_type:
        return 'VARCHAR(255)'  # 默认类型

    mssql_type_lower = mssql_type.lower().strip()

    # 处理字符串类型的特殊情况
    if mssql_type_lower in ['char', 'varchar', 'nchar', 'nvarchar']:
        # 如果max_length为-1（MSSQL中表示MAX）或None，使用TEXT
        if max_length is None or max_length == -1 or max_length > 65535:
            return 'TEXT'
        elif max_length > 16383:
            # MySQL VARCHAR最大长度限制（考虑utf8mb4字符集）
            return 'TEXT'
        elif max_length > 0:
            if mssql_type_lower in ['char', 'nchar']:
                # NCHAR是Unicode，需要考虑字符集
                return f'CHAR({min(max_length, 255)})'
            else:
                return f'VARCHAR({max_length})'
        else:
            # 默认长度
            if mssql_type_lower in ['char', 'nchar']:
                return 'CHAR(1)'
            else:
                return 'VARCHAR(255)'

    # 处理二进制类型的特殊情况
    if mssql_type_lower in ['binary', 'varbinary']:
        if max_length is None or max_length == -1 or max_length > 65535:
            return 'LONGBLOB'
        elif max_length > 255:
            return 'BLOB'
        elif max_length > 0:
            if mssql_type_lower == 'binary':
                return f'BINARY({max_length})'
            else:
                return f'VARBINARY({max_length})'
        else:
            return 'VARBINARY(255)'

    # 处理精度和标度的数值类型
    if mssql_type_lower in ['decimal', 'numeric']:
        # 使用实际的精度和标度
        precision = numeric_precision if numeric_precision is not None else 18
        scale = numeric_scale if numeric_scale is not None else 0

        # MySQL DECIMAL最大精度是65
        precision = min(precision, 65)
        scale = min(scale, 30)  # 标度不能超过精度

        if scale > precision:
            scale = precision

        return f'DECIMAL({precision},{scale})'

    # 其他数据类型的映射
    type_mapping = {
        'int': 'INT',
        'bigint': 'BIGINT',
        'smallint': 'SMALLINT',
        'tinyint': 'TINYINT',
        'bit': 'TINYINT(1)',  # MySQL中BOOLEAN是TINYINT(1)的别名
        'money': 'DECIMAL(19,4)',
        'smallmoney': 'DECIMAL(10,4)',
        'float': 'DOUBLE',  # MSSQL的float对应MySQL的DOUBLE
        'real': 'FLOAT',
        'datetime': 'DATETIME',
        'datetime2': 'DATETIME(6)',  # datetime2支持更高精度
        'smalldatetime': 'DATETIME',
        'date': 'DATE',
        'time': 'TIME',
        'datetimeoffset': 'DATETIME(6)',  # 时区信息会丢失
        'timestamp': 'BINARY(8)',  # MSSQL的timestamp是rowversion的同义词
        'rowversion': 'BINARY(8)',  # 用于版本控制，不是时间戳
        'text': 'TEXT',
        'ntext': 'TEXT',
        'image': 'LONGBLOB',
        'xml': 'TEXT',
        'uniqueidentifier': 'CHAR(36)',  # GUID格式
        'sql_variant': 'TEXT',
        'geography': 'TEXT',  # 空间数据类型转为TEXT
        'geometry': 'TEXT',
        'hierarchyid': 'VARCHAR(255)'
    }

    result = type_mapping.get(mssql_type_lower, 'VARCHAR(255)')

    # 确保返回的类型不为空
    if not result or result.strip() == '':
        return 'VARCHAR(255)'

    return result

def update_config_status(config_id, status, message):
    """更新配置表中的最终状态"""
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    cursor.execute('''
        UPDATE configurations 
        SET final_status = ?, final_message = ?, updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    ''', (status, message, config_id))
    conn.commit()
    conn.close()

def log_migration(config_id, status, message):
    """记录迁移日志"""
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    cursor.execute('''
        INSERT INTO migration_logs (config_id, status, message)
        VALUES (?, ?, ?)
    ''', (config_id, status, message))
    conn.commit()
    conn.close()

@app.route('/api/admin/databases')
def get_databases():
    if 'admin_logged_in' not in session:
        return jsonify({'error': '未授权'}), 401
    
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT c.*, 
               (SELECT COUNT(*) FROM migration_logs ml WHERE ml.config_id = c.id) as log_count,
               COALESCE(c.final_status, 
                       (SELECT status FROM migration_logs ml WHERE ml.config_id = c.id ORDER BY created_at DESC LIMIT 1),
                       'pending') as last_status
        FROM configurations c
        ORDER BY c.created_at DESC
    ''')
    
    databases = []
    for row in cursor.fetchall():
        databases.append({
            'id': row[0],
            'mssql_host': row[1],
            'mssql_database': row[2],
            'mssql_port': row[3],
            'mssql_username': row[4],
            'mysql_host': row[6],
            'mysql_database': row[7],
            'mysql_port': row[8],
            'mysql_username': row[9],
            'created_at': row[11],
            'final_status': row[12],
            'final_message': row[13],
            'updated_at': row[14],
            'log_count': row[15],
            'last_status': row[16]
        })
    
    conn.close()
    return jsonify(databases)

@app.route('/api/admin/logs/<int:config_id>')
def get_migration_logs(config_id):
    if 'admin_logged_in' not in session:
        return jsonify({'error': '未授权'}), 401
    
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT status, message, created_at
        FROM migration_logs
        WHERE config_id = ?
        ORDER BY created_at DESC
    ''', (config_id,))
    
    logs = []
    for row in cursor.fetchall():
        logs.append({
            'status': row[0],
            'message': row[1],
            'created_at': row[2]
        })
    
    conn.close()
    return jsonify(logs)

# 删除迁移记录的API
@app.route('/api/admin/delete_migration/<int:config_id>', methods=['DELETE'])
def delete_migration(config_id):
    if 'admin_logged_in' not in session:
        return jsonify({'error': '未授权'}), 401
    
    try:
        conn = sqlite3.connect('database.db')
        cursor = conn.cursor()
        
        # 先删除相关的日志记录
        cursor.execute('DELETE FROM migration_logs WHERE config_id = ?', (config_id,))
        
        # 再删除配置记录
        cursor.execute('DELETE FROM configurations WHERE id = ?', (config_id,))
        
        if cursor.rowcount == 0:
            return jsonify({'success': False, 'message': '记录不存在'}), 404
        
        conn.commit()
        conn.close()
        
        return jsonify({'success': True, 'message': '删除成功'})
    except Exception as e:
        return jsonify({'success': False, 'message': f'删除失败: {str(e)}'}), 500

# 批量删除迁移记录的API
@app.route('/api/admin/delete_migrations', methods=['DELETE'])
def delete_migrations():
    if 'admin_logged_in' not in session:
        return jsonify({'error': '未授权'}), 401
    
    data = request.get_json()
    config_ids = data.get('config_ids', [])
    
    if not config_ids:
        return jsonify({'success': False, 'message': '未提供要删除的记录ID'}), 400
    
    try:
        conn = sqlite3.connect('database.db')
        cursor = conn.cursor()
        
        # 先删除相关的日志记录
        placeholders = ','.join(['?' for _ in config_ids])
        cursor.execute(f'DELETE FROM migration_logs WHERE config_id IN ({placeholders})', config_ids)
        
        # 再删除配置记录
        cursor.execute(f'DELETE FROM configurations WHERE id IN ({placeholders})', config_ids)
        
        deleted_count = cursor.rowcount
        conn.commit()
        conn.close()
        
        return jsonify({'success': True, 'message': f'成功删除 {deleted_count} 条记录'})
    except Exception as e:
        return jsonify({'success': False, 'message': f'批量删除失败: {str(e)}'}), 500

if __name__ == '__main__':
    init_db()
    app.run(debug=True, port=7629, host='0.0.0.0')

# 进度查询接口（需管理员登录）
@app.route('/api/admin/progress/<int:config_id>')
def get_progress(config_id):
    if 'admin_logged_in' not in session:
        return jsonify({'error': '未授权'}), 401
    info = progress_info.get(config_id)
    if not info:
        return jsonify({'status': 'unknown'})
    total = info.get('total', 0) or 0
    done = info.get('done', 0) or 0
    percent = round((done / total * 100), 2) if total > 0 else 0
    result = dict(info)
    result['percent'] = percent
    return jsonify(result)